<?php
/**
 +------------------------------------------------------------------------------
 * AssetsreportAction控制器类
 +------------------------------------------------------------------------------
 * @category   资产报表
 * @package  app
 * @subpackage  Action
 * @author   朝闻道 <tb>
 * @date 2014.03.17
 * @time  下午2:44:22
 +------------------------------------------------------------------------------
 */
class AssetsreportAction extends BaseAction {
	protected $dao,$changt_str;
	
	function _initialize() {
		parent::_initialize ();
		$this->changt_str=array(
				'ADMIN_ADD'=>'财务充值',
				'ORDER_USE'=>'订单消费',
				'ORDER_BAK'=>'订单退款',
				'PV_CHANG'=>'PV变动',
				'MV_CHANG'=>'MV变动',
				'TENPAY_ADD'=>'财付通',
				'ALIPAY_ADD'=>'支付宝',
				'COUPON_CHANG'=>'PV换优惠券',
				'ADMIN_BV' => '赠送BV',
				'CVTOMV' => 'CV转MV',
				'couponxfcz' => '赠送优惠券',
				'couponxf' => '优惠券'
		);
		$this->dao = M("UserAssets");
		$this->assign('changtype',$this->changt_str);
	}
	
	/**
	 * 资金总报表
	 * @author tb
	 * @date 2014.03.24
	 * */
	function zj(){
		//充值集合
		$list['cz']['data'] = self::asset_report("'ADMIN_ADD','TENPAY_ADD','ALIPAY_ADD' ","'CV'");
		//消费充值集合
		$list['xfcz']['data'] = self::asset_report("'ORDER_BAK','ADMIN_BV'","'CV'");
		$coupon = self::coupon_total();
		$cxfcz['changtype'] = 'couponxfcz';
		$cxfcz['subject'] = 'CV';
		$cxfcz['value'] = $coupon['total'];
		$cxfcz['coupon'] = 'coupon|xfcz'; //此组数据为优惠券
		$list['xfcz']['data'][] = $cxfcz;
		//消费集合
		$list['xf']['data'] = self::asset_report("'ORDER_USE'", "'CV'");
		$cxf['changtype'] = 'couponxf';
		$cxf['value'] = $coupon['use'];
		$cxf['subject'] = 'CV';
		$cxf['coupon'] = 'coupon|xf'; //此组数据为优惠券
		$list['xf']['data'][] = $cxf;
		
		foreach ($list as $key=>$value){
			foreach ($value['data'] as $vo){
				$list[$key]['total']['CV'] += $vo['value']; 
			}
			$list[$key]['total']['count'] += count($value['data'])+1;
		}
		$this->assign('list',$list);
		$this->display();
	}
	
	/**
	 * 充值报表
	 * @author tb
	 * @date 2014.03.17
	 * */
	function cz(){
		
		$subject = array(
			'CV' => 1,
			'BV' => 1
		);
		$changtype = "'ADMIN_ADD','TENPAY_ADD','ALIPAY_ADD' ";
		$res = self::asset_report($changtype);
		$this->assign('list',$res);
		$this->display();
	}
	
		
	
	/**
	 * 消费充值报表
	 * @author tb
	 * @date 2014.03.18
	 * */
	function xfcz(){
		$changtype = "'ORDER_BAK','ADMIN_BV'";
		$res = self::asset_report($changtype);
		$changtype = "'ORDER_USE'";
		$o_res = self::asset_report($changtype,"'PV'");
		$list = array_merge ($res , $o_res );
		//获取赠送优惠券的值
		$coupon = self::coupon_total();
		$this->assign('list',$list);
		$this->assign('coupon',$coupon);
		$this->display();
	}
	
	/**
	 * 消费报表
	 * @author tb
	 * @date 2014.03.18
	 * */
	function xf(){
		$changtype = "'ORDER_USE'";
		$res = self::asset_report($changtype,"'BV','CV','MV'");
		
		//获取赠送优惠券的值
		$coupon = self::coupon_total();
		$this->assign('coupon',$coupon);
		$this->assign('list',$res);
		$this->display();
	}
	
	/**
	 * CV 汇总统计
	 * @author tb
	 * @date 2014.03.26
	 * */
	function cvhz(){
		$rate = array(
			'TENPAY_ADD' => '0.4%' ,
			'ALIPAY_ADD' => '1.2%'
		);
		//充值 CV
		$czlist = self::asset_report("'ADMIN_ADD','TENPAY_ADD','ALIPAY_ADD' ","'CV'");
		//获取赠送优惠券的值
		$coupon = self::coupon_total();
		$couponcz['changtype'] = "couponxfcz";
		$couponcz['subject'] = "CV";
		$couponcz['value'] = $coupon['total'];
		$czlist[] = $couponcz;
		
	   // 消费 CV	   
		$xflist = self::asset_report("'ORDER_USE'","'CV'");
		$couponxf['changtype'] = "couponxf";
		$couponxf['subject'] = "CV";
		$couponxf['value'] = $coupon['use'];
		$xflist[] = $couponxf;
		
		//日结余数据
		$date = date("Y-m-d", strtotime("1 days ago"));
		$time['btime'] = gettime($date." 00:00:00");
		$time['etime'] = gettime($date." 23:59:59");
		
		$MSdao = M("MoneyStatistics");
		//日结余
		$daylist = $MSdao->where('type=1')->order('start_date desc')->limit(5)->select();
		$this->assign('daylist',$daylist);
		//周结余
		$weeklist = $MSdao->where('type=2')->order('start_date desc')->limit(5)->select();
		$this->assign('weeklist',$weeklist);
		//月结余
		$monthlist = $MSdao->where('type=3')->order('start_date desc')->limit(5)->select();
		$this->assign('monthlist',$monthlist);

		
		$this->assign('czlist',$czlist);
		$this->assign('xflist',$xflist);
		$this->assign('czcount',count($czlist)+1);
		$this->assign('xfcount',count($xflist));
		$this->assign('rate',$rate); 
		$this->display();
	}
	
	
	
	/**
	 * 用户与额统计
	 * @author tb
	 * @date 2014.03.18
	 * */
	function umoney(){
		$Udao = M("User");
		$UMdao = M("User");
		$where = array();
		$where['platform'] = 'SMW01';
		if($_REQUEST['sfield']){
			$where[$_REQUEST['sfield']] = array('LIKE','%'.$this->keywords.'%');
			$wheresql = " and ".$_REQUEST['sfield']." like '%".$this->keywords."%' ";
			$this->assign("sfield",$_REQUEST['sfield']);
		}
		if($this->order){
			$order = " order by ".$this->order;
		}
		$count = $Udao->join(" as u left join user_ext as ue on u.id = ue.userid")->where($where)->count();
		$page=new Page($count,$this->listrows);
		$offset = $page -> firstRow;
		$length = $page -> listRows;
		
		
		$sql = "select round(sum(um.`value`/100),2) as sum , um.`subject` from `user` as u left join  user_money as um on u.id = um.userid where platform='SMW01' ".$wheresql." group by um.`subject`";
		$resum = $Udao->query($sql);
			
		foreach ($resum as $s){
			$sum[$s['subject']] = $s['sum'];
		}

		$sql = "select us.id,us.username,us.mobile,
	(select round(SUM(`value`)/100,2) CV from user_money um WHERE platform ='SMW01' and us.id=userid and `subject` = 'CV') CV,
	(select round(SUM(`value`)/100,2) BV from user_money um WHERE platform ='SMW01' and us.id=userid and `subject` = 'BV') BV,
	(select round(SUM(`value`)/100,2) MV from user_money um WHERE platform ='SMW01' and us.id=userid and `subject` = 'MV') MV,
	(select round(SUM(`value`)/100,0) PV from user_money um WHERE platform ='SMW01' and us.id=userid and `subject` = 'PV') PV,
	(select round(SUM(`value`)/100,0) CNY from user_money um WHERE platform ='SMW01' and us.id=userid and `subject` = 'CNY') CNY
	from `user` us where 1=1 ".$wheresql." ".$order." limit ".$offset.",".$length;
	
		$list = $Udao->query($sql);
	
		$this->assign("totalCount",$count);
		$this->assign('list',$list);
		$this->assign('sum',$sum);
		$this->display();
	}
	
	
	/**
	 * 用户与额统计
	 * @author tb
	 * @date 2014.03.18
	 * */
	function umoney_bak(){
		$Udao = M("User");
		$UMdao = M("User");
		$where = array();
		$where['platform'] = 'SMW01';
		if($_REQUEST['sfield']){
			$where[$_REQUEST['sfield']] = $this->keywords;
			$wheresql = " and ".$_REQUEST['sfield']." like '%".$this->keywords."%' ";
			$this->assign("sfield",$_REQUEST['sfield']);
		}
		if($this->order){
			$order = " order by ".$this->order;
		}
		//$where = self::create_where($where);
		$count = $Udao->join(" as u left join user_ext as ue on u.id = ue.userid")->where($where)->count();
		//$count = count($res);
		$page=new Page($count,$this->listrows);
		$offset = $page -> firstRow;
		$length = $page -> listRows;
		
		$list = $Udao->field('u.id as id,username,mobile')->join(" as u left join user_ext as ue on u.id = ue.userid")->limit($offset,$length)->where($where)->select();
		
		foreach ($list as $vo){
			$uids[] = $vo['id'];
		}
		
		$uids = implode(',', $uids);
		
		$sql = "select u.id as id,userid,username, mobile , 
				ROUND(SUM(case `subject` when 'CV' then `value` ELSE 0 end)/100,2) CV,
				ROUND(SUM(case `subject` when 'BV' then `value` ELSE 0 end)/100,2) BV,
				ROUND(SUM(case `subject` when 'MV' then `value` ELSE 0 end)/100,2) MV,
				ROUND(SUM(case `subject` when 'PV' then `value` ELSE 0 end)/100,0) PV
				from `user` as u LEFT JOIN user_money  as um on um.userid = u.id  WHERE platform ='SMW01'".$wheresql." group by userid  ".$order."  limit ".$offset.",".$length;
		
		$sql = "select userid, 
				ROUND(SUM(case `subject` when 'CV' then `value` ELSE 0 end)/100,2) CV,
				ROUND(SUM(case `subject` when 'BV' then `value` ELSE 0 end)/100,2) BV,
				ROUND(SUM(case `subject` when 'MV' then `value` ELSE 0 end)/100,2) MV,
				ROUND(SUM(case `subject` when 'PV' then `value` ELSE 0 end)/100,0) PV
				from user_money where userid in (".$uids.") group by userid  ".$order ;

		$umlist = $UMdao->query($sql);
		$umlist = field2key($umlist,'userid');
		$list = field2key($list,'id');
		
		$this->assign('umlist',$umlist);
		$this->assign("totalCount",$count);
		$this->assign('list',$list);
		$this->display();
	}
	
	

	
	/**
	 * 转账统计列表
	 * @author tb
	 * @date 2014.03.18
	 * */
	function zz(){
		$changtype = "'COUPON_CHANG','CVTOMV'";
		$res = self::asset_report($changtype);
		$this->assign('list',$res);
		$this->display();
	}
	
	
	
	/**
	 * 消费充值报表
	 * @author tb
	 * @date 2014.03.18
	 * */
	function asset_report($changtype,$subject,$time){		
		$where = '';
		if($subject){
			$where .= " and `subject` in ($subject) ";
		}
		$conditon['starttime'] = $_REQUEST['starttime'];
		$conditon['endtime'] = $_REQUEST['endtime'];
		$starttime = gettime($_REQUEST['starttime']." 00:00:00");
		$endtime = gettime($_REQUEST['endtime']." 23:59:59");
		$conditon['t_start'] = $starttime;
		$conditon['t_end'] = $endtime;
		if($starttime && $endtime){
			$where .= " and ctime between $starttime and $endtime ";
		}elseif($starttime){
			$where .=" and (ctime > $starttime or ctime = $starttime) ";
		}elseif($endtime){
			$where .= " and (ctime < $endtime or ctime = $endtime) ";
		}
		$sql = "select changtype,`subject`,round(sum(`value`)/100,2) as value from user_assets where platform = 'SMW01' and changtype in (".$changtype.")".$where."  GROUP BY changtype,`subject`";
		$res = $this->dao->query($sql);
		$this->assign('con',$conditon);
		return $res;
	}
	
	/**
	 * 用户优惠券统计
	 * @author tb
	 * @date 2014.03.18
	 * */
	function coupon_total(){
		$dao = M("UserCoupon");
		$where = array();
		$starttime = gettime($_REQUEST['starttime']." 00:00:00");
		$endtime = gettime($_REQUEST['endtime']." 23:59:59");
		$where = parent::create_date(array('stime'=>$starttime,'etime'=>$endtime),'createtime',$where);
		$coupon['total'] = round($dao->where($where)->sum("value/100"),2);
		$where['status'] = 1;
		$coupon['use'] = round($dao->where($where)->sum("value/100"),2);
		$where['status'] = 0;
		$coupon['nouse'] = round($dao->where($where)->sum("value/100"),2);
		return $coupon; 
	}
	
	
	/**
	 * 通过changtype 统计 BV CV ...(通用)
	 * @author tb
	 * @date 2014.03.17
	 * @return array('CV'=>1,'BV'=>1,'Pv'=>1.。。。)
	 * */
	function total_by_ct($changtype){
		$sql = "select `subject`,round(sum(`value`)/100,2) as value from user_assets where changtype = $changtype GROUP BY `subject`";
		$res = $this->dao->query($sql);
		$res = field2key($res,'subject');
		return $res;
	}
	

	/**
	 * 饼状图形报表
	 * @author tb
	 * @date 2014.03.20
	 * */
	function piechart(){
		$changtype = array();$subject= array();
		$list = array();
		if ( $_REQUEST['todo'] == 'piechart' ) {
			$list = self::chart();
			
			foreach ($list as $vo){
				$pieData .= abs($vo['value']).",";
				$pieLegend[] = "%%.%%%% – ".$vo['subject']." - ".$vo['value']." - ".$this->changt_str[$vo['changtype']];
			}
	
			$pieData = substr($pieData, 0,strlen($pieData)-1);
			$pieLegend =json_encode($pieLegend);
				
			//$pieData = "55973.91,79846746.33,16981,44610";
			$this->assign("pieData",$pieData);
			$this->assign("pieLegend",$pieLegend);
			$this->display();
		}else{
			$re['ids'] = implode(',',$_REQUEST['ids']);
			$re['ftodo'] = $_REQUEST['ftodo'];
			$re['action'] = 'piechart';
			die( json_encode( $re ) );
			//$this->display();
		}
	
	}

	/**
	 * 柱状图形报表
	 * @author tb
	 * @date 2014.03.22
	 * */
	function barchart(){
		$changtype = array();$subject= array();
		if ( $_REQUEST['todo'] == 'barchart' ) {
			//设置图形 宽度 
			$barwidth = empty($_REQUEST['barwidth']) ? 550 : $_REQUEST['barwidth'];
			$list = self::chart();
			foreach ($list as $vo){
				$barData[0][]  = abs($vo['value']);
				$pieLabel[0][] = $this->changt_str[$vo['changtype']]." ".$vo['subject'];
			}
			$barData = empty($barData) ? array(array(0)) : $barData; //柱状数据
			$pieLabel = empty($pieLabel) ? array(array('无')) : $pieLabel; //柱状显示文本
			$barData =json_encode($barData);
			$pieLabel =json_encode($pieLabel);			
			$this->assign("barData",$barData);
			$this->assign("pieLabel",$pieLabel);
			$this->assign("barwidth",$barwidth);
			$this->display();
		}else{
			$re['ids'] = implode(',',$_REQUEST['ids']);
			$re['ftodo'] = $_REQUEST['ftodo'];
			$re['action'] = 'barchart';
			die( json_encode( $re ) );
			//$this->display();
		}
	}
	
	
	/**
	 * 选择科目的统计列表
	 * @author tb
	 * @date 2014.03.22
	 * */
	function chart(){
		$ids = $_REQUEST['ids'] ;
		$ids = explode(',',$ids);		
		$list = array();
		foreach ($ids as $str){
			$res = $arr = array();		
			$arr = explode('|', $str);
			if($arr[0] == "coupon"){
				$coupon = self::coupon_total();
				$coupon_data['subject'] = 'CV';
				if($arr[1] == 'xfcz'){
					$coupon_data['changtype'] = 'couponxfcz';
					$coupon_data['value'] = $coupon['total'];
							
				}elseif($arr[1] == 'xf'){
					$coupon_data['changtype'] = 'couponxf';
					$coupon_data['value'] = $coupon['use'];
				}
			    $res[] = $coupon_data;
				$list = array_merge($list,$res);
			}else{		
				$changtype = "'".$arr[0]."'";
				$subject = "'".$arr[1]."'";
				$res = self::asset_report($changtype,$subject);
				$list = array_merge($list,$res);
			}				
			
				
		}
		return  $list;
	}
	
	
}//end AssetsreportAction